# -*- coding:utf-8 -*-
__author__ = 'hyq'

import pypyodbc
import os,datetime,openpyxl,re
import PySimpleGUI as sg
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

bd = Border(left=Side(border_style="thin",
              color='FF001000'),
    right=Side(border_style="thin",
               color='FF110000'),
    top=Side(border_style="thin",
             color='FF110000'),
    bottom=Side(border_style="thin",
                color='FF110000'),
    diagonal=Side(border_style=None,
                  color='FF000000'),
    diagonal_direction=0,
    outline=Side(border_style=None,
                 color='FF000000'),
    vertical=Side(border_style=None,
                  color='FF000000'),
    horizontal=Side(border_style=None,
                   color='FF110000')
                )

class Window(sg.Window):
    def __init__(self):
        super().__init__('短暂停分析工具')
        self._path = getDesktopPath()
        self._months = [datetime.datetime.today().month,datetime.datetime.today().month-1]
        self._days = [x for x in range(1,32)]
        self.show()

    def show(self):
        sg.theme('GreenMono')
        frame_layout = [
            [sg.Radio('白班', "RADIO1", default=True, key='daytime'),
             sg.Radio('夜班', "RADIO1", key='night')],
        ]

        self.layout = [
            [sg.Text('请选择日期',size=(18,1)),sg.Combo(values=self._months,size=(4,1),key='month',default_value=self._months[0]),sg.Text('月'),
             sg.Combo(values=self._days,size=(4,1),key='day',default_value=datetime.datetime.today().day-1),sg.Text('日'),sg.Frame('',frame_layout)],
            [sg.Text('请选择线体', size=(18, 1)), sg.Combo(values=list(jiting.keys()), size=(10, 1), key='line'),
             sg.Text('请选择时间段', size=(10, 1)),sg.Input(key='start',size=(4,1)),sg.Text('点  到  '),sg.Input(key='end',size=(4,1)),sg.Text('点')],
            [sg.Text('选择.mdb数据库文件',size=(18,1)),sg.Input(key='filename',size=(40,1)),sg.FileBrowse(button_text='打开')],
            [sg.Text('选择目标文件夹',size=(18,1)),sg.Input(key='local',default_text=self._path,size=(40,1)),sg.FolderBrowse(button_text='打开')],
            [sg.Ok(), sg.Exit()]
        ]
        self.root = sg.Window('短暂停分析工具', self.layout)

#定义数据库连接
def mdb_conn(db_name, password = ""):
    """
    功能：创建数据库连接
    :param db_name: 数据库名称
    :param db_name: 数据库密码，默认为空
    :return: 返回数据库连接
    """
    str = 'Driver={Microsoft Access Driver (*.mdb)};PWD' + password + ";DBQ=" + db_name
    conn = pypyodbc.win_connect_mdb(str)

    return conn

#增加记录
def mdb_add(conn, cur, sql):
    """
    功能：向数据库插入数据
    :param conn: 数据库连接
    :param cur: 游标
    :param sql: sql语句
    :return: sql语句是否执行成功
    """
    try:
        cur.execute(sql)
        conn.commit()
        return True
    except:
        return False

#删除记录
def mdb_del(conn, cur, sql):
    """
    功能：向数据库删除数据
    :param conn: 数据库连接
    :param cur: 游标
    :param sql: sql语句
    :return: sql语句是否执行成功
    """
    try:
        cur.execute(sql)
        conn.commit()
        return True
    except:
        return False

#修改记录
def mdb_modi(conn, cur, sql):
    """
    功能：向数据库修改数据
    :param conn: 数据库连接
    :param cur: 游标
    :param sql: sql语句
    :return: sql语句是否执行成功
    """
    try:
        cur.execute(sql)
        conn.commit()
        return True
    except:
        return False

# 查询记录
def mdb_sel(cur, sql):
    """
    功能：向数据库查询数据
    :param cur: 游标
    :param sql: sql语句
    :return: 查询结果集
    """
    try:
        cur.execute(sql)
        return cur.fetchall()
    except:
        return []

# 获得所有VarName（组态王的变量名称）
def get_locals(cur, tablename):
    '''
    :param cur: 数据库指针
    :param tablename: 数据库的表单名称。Alarm202002表示2月份，Alarm202003表示3月份
    :return: 返回一个二维数组，包含所有的VarName（组态王的变量名称）
    '''
    sql = "SELECT DISTINCT VarName FROM " + tablename
    data = mdb_sel(cur, sql)
    return data


def getDate(values):
    date = datetime.date(datetime.date.today().year,values['month'],values['day'])
    return date.strftime("%y/%m/%d")


# 获得某个日期白班、夜班的数据
def get_Data(cur, tablename,local,values):
    '''
    :param cur: 数据库指针
    :param tablename: 数据库的表单名称。Alarm202002表示2月份，Alarm202003表示3月份
    :param local: 急停位置名称，跟VarName一样，去掉前后空白
    :param values: values字典里面包含了月份、日期数据
    :return: 返回查询记录，一个二维列表
    '''
    # 标准化日期格式,today是当天，tomorrow是第二天
    today = getDate(values)
    new_values = {}
    new_values.setdefault('month', values['month'])
    new_values.setdefault('day', values['day'] + 1)
    tomorrow = getDate(new_values)
    # 定义sql语句并取得数据
    sql = "SELECT DISTINCT AlarmDate,AlarmTime,AlarmType,VarName FROM " + tablename \
          + " WHERE VarName = '%s'" % local + " AND " + ("AlarmDate LIKE '%s'" % today) + " ORDER BY AlarmTime"
    new_sql = "SELECT DISTINCT AlarmDate,AlarmTime,AlarmType,VarName FROM " + tablename \
          + " WHERE VarName = '%s'" % local + " AND " + ("AlarmDate LIKE '%s'" % tomorrow) + " ORDER BY AlarmTime"
    today_data = mdb_sel(cur, sql)
    tomorrow_data = mdb_sel(cur, new_sql)
    # 定义正则表达式
    # 定义白班的正则表达式，匹配0开头，第二位8或9（08,09），或者1开头，第二位0-9，即08点至19点
    Regex_daytime = re.compile(r'(^0[8-9]|^1[0-9]):\d\d:\d\d')
    # 定义夜班的正则表达式
    # 第二天的正则表达式，匹配00-07点
    Regex_night_tomorrow  = re.compile(r'^0[0-7]:\d\d:\d\d')
    # 当天的正则表达式，匹配20-23点
    Regex_night_today = re.compile(r'^2[0-3]:\d\d:\d\d')
    # 遍历data
    data = []
    if values['daytime']:       #白班的算法
        for d in today_data:
            mo = Regex_daytime.search(d[1])
            if mo is None:
                pass
            else:
                data.append(d)
    else:                       #夜班的算法
        for d in today_data:
            mo = Regex_night_today.search(d[1])
            if mo is None:
                continue
            else:
                data.append(d)
        for d in tomorrow_data:
            mo = Regex_night_tomorrow.search(d[1])
            if mo is None:
                continue
            else:
                data.append(d)
    return data

# 获得某个急停位置的名称按下急停次数
def get_num(data, keyword='按下'):
    num = 0
    for d in data:
        if keyword in d[2]:
            num += 1
    return num

# 计算两个时间之间的秒数
def time_between(close_time, open_time):
    '''
    计算急停松开的时间open_time，与急停按下时间close_time之间的时间差（秒）
    :param close_time:急停按下时间，较早的时间
    :param open_time: 急停松开时间，较晚的时间
    :return: 时间差多少秒
    '''
    a1 = open_time
    a2 = close_time
    test1 = datetime.datetime.strptime(a1, "%H:%M:%S")
    test2 = datetime.datetime.strptime(a2, "%H:%M:%S")
    diff = test2 - test1
    return diff.seconds

# 将时间格式化
def trans_time(string):
    '''
    :param string:数据库导出来的日期，字符串格式
    :return: 返回datetime.time格式对象
    '''
    time = string.strip().split(sep=':')
    hour = int(time[0])
    min = int(time[1])
    sec = int(time[2])
    return datetime.time(hour,min,sec)

# 获得桌面的绝对地址
def getDesktopPath():
    return os.path.join(os.path.expanduser("~"),'Desktop')

# W10线急停的名字清单
jiting = {
            'W10' : ['抽真空左边前段急','抽真空左边后段急','抽真空右边前段急','抽真空右边后段急','出提升笼到上底座',
                     '上底座到焊接急停','焊接到真空线急停','真空出口到灌注急','灌注到卤检急停','卤检到商检房急停',
                     '商检左边前段急停','商检右边后段急停','商检出口到静音房','静音房急停','静音房到夹抱机急',
                     '夹抱机急停','回板提升机急停','线尾包装急停','线头回板急停'],

            'W7' : ['线头回板机急停','线头第一段链板线','线头第二段链板线','真空线入口急停','真空线插线急停',
                    '真空线出口急停','封尾到卤检段急停','卤检段急停','上侧板段急停1','上盖板段急停',
                    '安参段急停','商检房入口急停','商检房出口急停','吹水段急停','吹水段到静音房急停',
                    '静音房段急停','静音房段到夹抱机急停','夹抱机急停1','线尾回板机机急停','线尾包装段急停'],

            'W6' : ['卤检到安参段急停','卤检段急停','商检出口到套箱急停','商检房入口急停','商检房出口急停',
                    '商检房环形线中急停','夹抱机急停','套箱到夹抱机急停','安参段急停','封尾段急停',
                    '真空线入口急停','真空线出口急停','真空线插线急停','线头链板（线中）','线头链板（线头）',
                    '线头链板（线尾）','线尾包装段急停'],

            'W5' : ['卤检到安参段急停','卤检段急停','商检出口到套箱急','商检出口到转盘急','商检房入口急停',
                    '商检房出口急停','商检房环形线中急','夹抱机急停','套箱到夹抱机急停','安参段急停',
                    '封尾段急停','真空线入口急停','真空线出口急停','真空线出口转盘急','真空线插线急停',
                    '线头上接头段','线头第一段链板（','线尾包装段急停','线尾转盘到套箱急']

            }

# 查询两个时间段之间的数据
def getBetween(data, start, end):
    if start == "" or end == "":
        return False
    elif int(end) <= int(start):
        return False
    if int(start) < 10:
        string_start = '0'+ str(start)
    else:
        string_start = str(start)
    if int(end) < 10:
        string_end = '0' + str(int(end)-1)
    else:
        string_end = str(int(end)-1)
    if int(end)- int(start) == 1:
        Regex = re.compile(r'{0}:\d\d:\d\d'.format(string_start))
    else:
        Regex = re.compile(r'[{0}-{1}]:\d\d:\d\d'.format(string_start, string_end))
    new_data = []
    for d in data:
        mo = Regex.search(d[1])
        if mo is None:
            continue
        else:
            new_data.append(d)
    return new_data


def get_time(select_data):
    total = 0
    for index in range(len(select_data) - 1, 0, -1):
        if '松开' in select_data[index][2] or '恢复' in select_data[index][2]:
            close_time = select_data[index][1].strip()
            # print('松开时间是：%s' % close_time)
            sep = 1
            while True:
                try:
                    if '按下' in select_data[index - sep][2]:
                        open_time = select_data[index - sep][1].strip()
                        # print('按下时间是：%s' % open_time)
                        break
                    sep -= 1
                except:
                    open_time = close_time
                    break
            time = time_between(close_time, open_time)
            if time > 300:
                time = 0
                # print('这是休息时间~~~')
            total = total + time
    return total


def insert_table_perhour():
    global md
    sheet.cell(row=1, column=col + 3).value = '{0}时-{1}时数据'.format(time, time + 1)
    sheet.merge_cells(start_row=1, start_column=col + 3, end_row=1, end_column=col + 4)
    sheet.cell(row=1, column=col + 3).alignment = Alignment(horizontal='center', vertical='center')
    sheet.cell(row=2, column=col + 3).value = '急停次数'
    sheet.cell(row=2, column=col + 4).value = '急停时间'
    md = getBetween(select_data, time, time + 1)
    sheet.cell(row=row, column=col + 3).value = get_num(md)
    sheet.cell(row=row, column=col + 4).value = get_time(md)


def gettime(data):
    total = 0
    for index in range(len(data) - 1, 0, -1):
        if '急停松开' in data[index][2]:
            close_time = data[index][1].strip()
            # print('松开时间是：%s' % close_time)
            sep = 1
            while True:
                if '急停按下' in data[index - sep][2]:
                    open_time = data[index - sep][1].strip()
                    # print('按下时间是：%s' % open_time)
                    break
                sep -= 1
            time = time_between(close_time, open_time)
            if time > 1800:
                time = 0
                # print('这是休息时间~~~')
            total = total + time
    return total


if __name__ == '__main__':
    window = Window()
    while True:
        event, values = window.root.read()
        print(event,values)
        if event == 'Ok':
            #try:
            # 数据库文件名称及位置
            pathfile = values['filename']
            # 数据库需要读取的表名称，202002代表2020年2月份
            if values['month'] < 10:
                tablename = 'Alarm20200{0}'.format(values['month'])
            else:
                tablename = 'Alarm2020{0}'.format(values['month'])
            # 连接数据库
            conn = mdb_conn(pathfile)
            cur = conn.cursor()
            # 获得表里面所有变量的名字
            locals_list = get_locals(cur, tablename)
            # 由于部分变量不是代表急停名字，故筛选只有在jiting字典里的变量名字
            locals = [x for x in locals_list if x[0].strip() in jiting[values['line']]]
            # 新建一个excel文件
            wb = openpyxl.Workbook()
            sheet = wb.active
            # 插入表头
            if values['daytime']:
                ban = '白班'
            else:
                ban = '夜班'
            sheet.append([values['line'] + ban + '线急停分析数据' ])
            # 合并第一行
            sheet.merge_cells('A1:C1')
            # 对齐正中
            sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')
            # 插入列名
            sheet.append(["急停点名称", "按下急停数量", "总暂停时间（秒）"])
            for local in locals:
                # values里面包含了月份、日期数据
                select_data = get_Data(cur, tablename, local[0], values)
                number = get_num(select_data)
                print("%s的按下次数是%i" % (local[0], number))
                sheet.append([local[0], number])
                #设置列宽度
                for c in ['A','B','C']:
                    sheet.column_dimensions[c].width = 18
            #插入短暂停数据从第3行开始
            row = 3
            # 获得各个急停点的停线时间
            for local in locals:
                select_data = get_Data(cur, tablename, local[0], values)
                if values['daytime']:
                    for time,col in zip(range(8,21),range(1,25,2)):
                        insert_table_perhour()
                else:
                    for time,col in zip(range(20,24),range(1,17,2)):
                        insert_table_perhour()
                    for time,col in zip(range(0,8),range(9,25,2)):
                        insert_table_perhour()


                # 获得
                total = get_time(select_data)
                print(local[0] + '急停时间是：%s 秒' % (total))
                sheet.cell(row=row,column=3).value = total
                row += 1
            cur.close()  # 关闭游标
            conn.close()  # 关闭数据库连接
            # 设置字体和表框
            for r in range(1, sheet.max_row + 1):
                for c in range(1, sheet.max_column + 1):
                    sheet.cell(r, c).font = Font(name=u'微软雅黑', size=10)
                    sheet.cell(r, c).border = bd
            wb.save(os.path.join(values['local'], "{0}分析结果.xlsx".format(values['line'])))
            sg.popup('结果已生成','已生成“分析结果.xlsx”，请查阅。')
            # except Exception as e:
            #     print(e)
            #     sg.popup('未选择正确的数据库文件！！！')
        elif event is None or event == 'Exit':
            break






    # #增
    # sql = "Insert Into " + tablename + " Values (33, 12, '天津', 0)"
    # if mdb_add(conn, cur, sql):
    #    print("插入成功！")
    # else:
    #    print("插入失败！")
    #
    # #删
    # sql = "Delete * FROM " + tablename + " where id = 32"
    # if mdb_del(conn, cur, sql):
    #    print("删除成功！")
    # else:
    #    print("删除失败！")
    #
    # #改
    # sql = "Update " + tablename + " Set IsFullName = 1 where ID = 33"
    # if mdb_modi(conn, cur, sql):
    #    print("修改成功！")
    # else:
    #    print("修改失败！")
    #
    # #查
    # sql = "SELECT * FROM " + tablename + " where id > 10"
    # sel_data = mdb_sel(cur, sql)
    # print(sel_data)

